The present invention relates generally to the field of query executions, and more particularly to enhancing query executions by choosing a more efficient access path.
A query plan (or query execution plan) is an ordered set of steps used to access data in a structured query language (SQL) relational database management system. The query is a specific case of the relational model concept of access paths. Since SQL is declarative, there are typically a large number of alternative ways to execute a given query, with widely varying performance. When a query is submitted to the database, the query optimizer evaluates some of the different, correct possible plans for executing the query and returns what the query optimizer considers the best alternative.
An access path refers to a path chosen by a system to retrieve data after a SQL request is executed. A query may request at least one variable to be filled up with one or more values. Access path selection can have a tremendous impact on the overall performance of the system. A query can be a very simple query with one variable being matched to values from one table only, or a more complex query that may involve looking for many variables which can be matched to many different records on separate tables. Some of these variables even have complex conditions such as greater than or less than some value of integers.
Optimization of access path selection may be gauged using cost formulas with input and output (I/O) and central processing unit (CPU) utilization weight usually considered. Generally, query optimizers evaluate the available paths to data retrieval and estimate the cost in executing the statements using the determined paths or a combination of these paths.